package com.ce.cases.assit.action;


import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.ce.cases.assit.dao.po.CaseAssitCheck;
import com.ce.cases.report.logic.IReportIllegalLogic;
import com.ce.framework.datamaintain.action.DataMaintainAction;
import com.ce.framework.datamaintain.dao.po.AbstractPO;
import com.ce.framework.exception.AppException;
import com.ce.framework.util.BeanAccessUtil;
import com.ce.framework.util.StringUtil;
import com.ce.sys.login.model.User;
import com.ce.util.DbUtil;
import org.apache.struts2.ServletActionContext;
import util.SessionJson;
import util.cls.CLASS;
import util.db.DB;

import util.json.EsayJsonEn;
import util.time.time;

import javax.servlet.http.HttpServletRequest;


/**
* 嫦娥软件有限公司 版权所有 2013
* 创建人：　andy
* 创建日期：2014-03-25
* 创建时间：13:23:23
* 功能描述：受托协查台账 [CASE_ASSIT_CHECK]更改时用的Action
* ==============================================
* 修改历史
* 修改人		修改时间		修改原因
*
* ==============================================
*/
public class CaseAssitCheckAction extends DataMaintainAction 
{
	private static final long serialVersionUID = 1L;
	private IReportIllegalLogic reportIllegalLogic;


	private String  oraaddress;
	private String  orauser;
	private String  orapwd;
	private Integer page=1;




	public Integer getPage() {
		return page;
	}

	public void setPage(Integer pag) {
		this.page = pag;
	}




	@Override
	protected String addItem() throws AppException {
		try {
			BeanAccessUtil.copyProperty(po, "registerDate", new Date());
			BeanAccessUtil.copyProperty(po, "registerPerson",User.getUserName());
			BeanAccessUtil.copyProperty(po, "registerDeptId",User.getUserOrgId());
		} catch (Exception e) {
			e.printStackTrace();
		}
		return super.addItem();
	}

	@Override
	protected String modifyItem() throws AppException {
		CaseAssitCheck entiy = (CaseAssitCheck) this.baselogic.getPOByPk(po.getClass().getName(), po.getVcId());
		BeanAccessUtil.copyNoCopyNullProperty(po, entiy);
		this.baselogic.updatePo(entiy, this.getAddList(), this.getModifyList(),this.getDeleteList());
		return super.searchData();
	}

	/**
	 * 打开回复操作页
	 */
	public String replyPage() throws AppException {
		String vcId = this.getSelectId();
	    try {
	    	po=(AbstractPO)this.baselogic.getPOByPk(po.getClass().getName(), vcId);
	    	CaseAssitCheck check=(CaseAssitCheck)po;
	    	if(check.getReplyDate()==null)
			    BeanAccessUtil.copyProperty(po, "replyDate", new Date());
		} catch (Exception e) {
			e.printStackTrace();
		}
		return "openReplyPage";
	}

	/**
	 * 回复保存
	 */
	public String dealReply() {
		try {
			CaseAssitCheck savePo = (CaseAssitCheck) this.baselogic.getPOByPk(po.getClass().getName(), po.getVcId());
			//注：修改时无法拷贝置空(非必填项)的数据，适合数据添加
			BeanAccessUtil.copyNoCopyNullProperty(po, savePo);
			this.baselogic.updatePo(savePo);
		} catch (AppException e) {
			e.printStackTrace();
			return this.returnInfoForJS(false, e.getMessage());
		}
		return this.returnInfoForJS(true, "回复情况保存成功！");
	}

	/**
	 * 打开入库情况操作页
	 */
	public String inWareHousePage() throws AppException
	{
		System.out.print("++++++++++++++++++++"+util.time.time.getYyyy_MM_dd()+"++++++++++++++++++++");

		String vcId = this.getSelectId();
	    po=(AbstractPO)this.baselogic.getPOByPk(po.getClass().getName(), vcId);
		return "openInWarehousePage";
	}

	/**
	 * 入库情况处理
	 */
	public String dealInResult() throws AppException {

		try {
			CaseAssitCheck savePo = (CaseAssitCheck) this.baselogic.getPOByPk(po.getClass().getName(), po.getVcId());
			//注：修改时无法拷贝置空(非必填项)的数据，适合数据添加
			BeanAccessUtil.copyNoCopyNullProperty(po, savePo);
			this.baselogic.updatePo(savePo);
		} catch (AppException e) {
			e.printStackTrace();
			return this.returnInfoForJS(false, e.getMessage());
		}
		return this.returnInfoForJS(true, "入库情况保存成功！");
	}

	/**
	* 周路--
	* 2015年10月12日10:07:24
	* */
	public  String get_xc_ListJson_Remote_ListJsp()
	{

		return "get_xc_ListJson_Remote_ListJsp";
	}

	/**
	 * 周路--
	 * 2015年10月12日10:07:24
	 * */
	public  void get_xc_ListJson_Remote()
	{
		/* 0 初始化*/
		HttpServletRequest request = ServletActionContext.getRequest();
		List<Object> caseAssitCheck_list = null;
		Connection con_a = null, con_b = null;

		/* 1 页面查询字段 */
		String request_assitType = request.getParameter("search.assitType");    //协查类型2

		if("2".equals(request_assitType))
		{
			System.out.println("进来了进行判断：request_assitType="+request_assitType);
			this.get_xc_ListJson_Remote_js();
		}
		else
		{
			return ;
		}

	}


	public String execute() throws Exception
	{
		/* 0 初始化- 1 页面查询字段 */
		HttpServletRequest 	request 			= ServletActionContext.getRequest();
		String 				request_assitType 	= request.getParameter("search.assitType");    //协查类型2

		if("2".equals(request_assitType))
		{
			System.out.println("进来了进行判断：request_assitType=" + request_assitType);
			this.get_xc_ListJson_Remote_js();
			return  SUCCESS;
		}
		else
		{
			return super.execute();
		}
	}
		/**
		 * 周路--
         * 获取远程数据库-数据（稽查系统）
         * @throws AppException
         */

	public  void get_xc_ListJson_Remote_js()
	{
		/* 0 数据库链接-初始化*/
			HttpServletRequest 	request				=	ServletActionContext.getRequest();
			List 				caseAssitCheck_list	=	null;
			Connection 			con_a				=	null;
			Connection			con_b				=	null;

			try
			{
				System.out.println("oraaddress:>>>>>>>>>>"+oraaddress+"   user:>>>>>>>>>>"+orauser+"  pwd:>>>>>>>>>>"+orapwd);
				con_a= DbUtil.getConnection(Class.forName("oracle.jdbc.driver.OracleDriver"), oraaddress, orauser, orapwd);
				//con_a= DbUtil.getConnection(Class.forName("oracle.jdbc.driver.OracleDriver"), "jdbc:oracle:thin:@//localhost:1521/orcl", "jc", "jc");
				//con_a=DbUtil.getConnection(Class.forName("oracle.jdbc.driver.OracleDriver"), "jdbc:oracle:thin:@//98.12.102.73:1521/cqsthxqf", "wsbs_ysq", "wsbs_ysq");
			}
			catch (ClassNotFoundException e1)	{	e1.printStackTrace();	}
			catch (Exception e1)				{	e1.printStackTrace();	}

			try
			{
				con_b= DbUtil.getConnection(Class.forName("oracle.jdbc.driver.OracleDriver"), oraaddress, orauser, orapwd);
				//con_b=DbUtil.getConnection(Class.forName("oracle.jdbc.driver.OracleDriver"), "jdbc:oracle:thin:@//98.12.102.73:1521/cqsthxqf", "wsbs_ysq", "wsbs_ysq");;
			}
			catch (ClassNotFoundException e1)	{	e1.printStackTrace();	}
			catch (Exception e1)				{	e1.printStackTrace();	}

		/* 1 页面查询字段 */
			String request_assitType	= request.getParameter("search.assitType");	//协查类型2
			String request_inspectDept	= request.getParameter("search.inspectDept");//15007010000稽查局
			String request_collectDept	= request.getParameter("search.collectDept");//15001090000税务局
			String request_involvedEnt	= request.getParameter("search.involvedEnt");//gdsm有限公司

			String regBeginDate 		= request.getParameter("search.regBeginDate");	//开始时间
			String regEndDate 			= request.getParameter("search.regEndDate");				//结束时间

			String  request_rows 		= request.getParameter("rows");//JSP列表分页数
			String  request_page 		= request.getParameter("page");//JSP列表分页码

			String 	where_str	="";//查询条件

		/* 2 列表查询（参数）初始化 */
				/* 2.1 查询条件>受托方税务机关代码 */

		/*		if(StringUtil.isNotBlank(request_inspectDept) && StringUtil.isNotBlank(request_collectDept))	//选择稽查局-税务局有值--不为空时
				{
					where_str+=" and stsd.swjg_dm_stf in ("+request_collectDept.substring(0,9)+") ";
				}
				else if (StringUtil.isNotBlank(request_inspectDept) && StringUtil.isBlank(request_collectDept))//选择稽查局--但是默认没有选择税务局（查询稽查局下面的所有税务局）
				{
					String	str	="";
					//查询本地--稽查局--所有税务局
					List 	li	=DB.get_Remote_list(DbUtil.getConnection(),"select swj_jc.VC_ID      from SYS_V_ORG$DEP  swj_jc     where  swj_jc.VC_PAR$ORGID='"+request_inspectDept+"'");

					for (Object obj:li)	{	str+=","+obj.toString().trim().substring(0,9);	}
					str=str.substring(1);

					where_str+=" and  stsd.swjg_dm_stf in ("+str+") ";
				}*/
				if(StringUtil.isNotBlank(request_inspectDept))
				{
					where_str+=" and  stsd.swjg_dm_stf in ("+request_inspectDept.substring(0,9)+") ";
				}
				System.out.println("受托方-税务机关代码:"+where_str);
				System.out.println("++++++++++++++++++++++++++++++++++++++>受托方-税务机关代码="+request_collectDept+"  稽查局 :"+request_inspectDept);

				/* 2.1 查询条件>查询时间*/

				if(StringUtil.isBlank(regBeginDate))	{			regBeginDate	= time.gettime("yyyy")+"-01-01";	System.out.println("++++++++++++++++++++++++++++++++++++++>where_str="+regBeginDate);	}//初始化时间
				if(StringUtil.isBlank(regEndDate))		{			regEndDate 		= time.gettime("yyyy")+"-12-31";	System.out.println("++++++++++++++++++++++++++++++++++++++>where_str="+regEndDate);	}//初始化时间
				if(StringUtil.isBlank(regBeginDate) && StringUtil.isBlank(regEndDate))
				{
					regBeginDate	= time.gettime("yyyy")+"-01-01";
					regEndDate 		= time.gettime("yyyy")+"-12-31";System.out.println("++++++++++++++++++++++++++++++++++++++>regBeginDate null 赋值后="+regBeginDate+" regEndDate null 赋值后="+regEndDate);
				}
				where_str+=" and stsd.RQ_DJ  >=TO_DATE ('"+regBeginDate+"', 'yyyy-mm-dd') and stsd.RQ_DJ <=TO_DATE ('"+regEndDate+"', 'yyyy-mm-dd')";
				System.out.println("++++++++++++++++++++++++++++++++++++++>where_str="+where_str);


			/* 2.2 Sql分页-查询参数*/
				Integer page	=1;	//初始化-当前页-开始记录
				Integer rows	=20;//初始化-当前页-结束记录
				int 	total_a	=0;	//初始化-查询记录总数
				if(null!=request_rows && null!=request_page)
				{
					rows = new Integer(request_rows);
					page = new Integer(request_page);
					System.out.println("++++++++++++++++++++++++++++++++++++++>request_rows="+request_rows);
				}
				page=(page-1)*rows;	//*********** Oracl 分页查询重要-计算当前页-开始记录 ***********
				rows=page+rows; 	//*********** Oracl 分页查询重要-计算当前面-结束记录 ***********
				page++; 			//*********** Oracl 分页查询重要-最后开始记录需要加1 ***********


		/* 3 sql查询语句---组装 */
			String sql_count			= "SELECT  COUNT (*) as total" +
															" FROM "+
															" xczh_stsd   stsd," +
															" xczh_stxc   stxc, " +
															" xczh_swcljg swcljg,  " +
															" xczh_wtxc   wtxc,  " +
															" xczh_xcjgd  xcjgd  " +
															"WHERE " +
															" stsd.xcbh=stxc.xcbh	 " +
															" and  stsd.xcbh=swcljg.xcbh" +
															" and  stsd.xcbh=wtxc.xcbh" +
															" and  stsd.xcbh=xcjgd.xcbh" +
															" and  stsd.swjg_dm_stf=stxc.swjg_dm_stf "+where_str ;//根据条件查询记录总数  " and  stsd.swjg_dm_stf=stxc.swjg_dm_stf "+where_str
String Remote_xczh_sql	="SELECT *"+
				" FROM ("+
				" SELECT   ROWNUM AS rowno,"+

				" stsd.xcbh   		 as assitTypeNum,"+   		//协查编号
				" wtxc.swjg_dm       as entrustDeptId,"+		//委托方税务机关代码
				" wtxc.swjg_mc       as entrustDept,"+			//
				" stsd.rq_dj  		 as registerDate,"+			//登记时间

				" xcjgd.fpfs_hj      as billCount,"+			//发票份数合计
				" xcjgd.je_hj        as amountMoney,"+			//金额合计
				" xcjgd.se_hj        as taxMoney,"+

				" stxc.swjg_dm_stf  as collectionDeptId,"+		//受托方税务机关代码
				" stxc.swjg_mc      as collectionDept,"+		//受托方机关代码

				" swcljg.nsrsbh     as taxpayerNumber,"+		//纳税人识别号
				" swcljg.nsrmc      as involvedEnterprice,"+	//纳税人名称-涉案企业
				" swcljg.fpfs_sjywt as problemBill,"+			//有问题发票份数

				" swcljg.je_sjywt 	as checkCounts,"+				//涉及有问题金额-	对应JSP列表-（应）查补数合计
				" swcljg.se_ycb 	as checkTax,"+					//应查补税额	-	对应JSP列表-（应）查补税款
				" swcljg.je_yfk 	as checkFine,"+					//应查补罚款	-	对应JSP列表-（应）查补罚款
				" swcljg.je_yjznj 	as checkLatefees,"+				//应交滞纳金	-	对应JSP列表-（应）查补滞纳金

				" swcljg.se_sjywt 	as inCounts,"+					//涉及有问题税额-	对应JSP列表-（应）（有问题税额）入库数合计
				" swcljg.se_yicb 	as inTax,"+						//已查补税额	-	对应JSP列表-（已）入库税款
				" swcljg.je_yifk 	as inFine,"+					//已罚款金额	-	对应JSP列表-（已）入库罚款
				" swcljg.je_yijznj 	as inLatefees,"+				//已交滞纳金额	-	对应JSP列表-（已）入库滞纳金

				" swcljg.sjlybbh 	as fromCode,"+				//数据来源版本号
				" swcljg.yjsfbmzt_dm as passJudiciary"+			//是否移送司法机关
				" FROM"+
				" xczh_stsd   stsd,"+	//协查整合-受托收到
				" xczh_stxc   stxc,"+	//协查整合-受托协查
				" xczh_wtxc   wtxc,"+	//协查整合-委托协查
				" xczh_swcljg swcljg,"+	//协查整合-税务处理结果
				" xczh_xcjgd  xcjgd"+	//协查整合-协查结果单

				" WHERE"+

				" stsd.xcbh=stxc.xcbh	  			and"+
				" stsd.xcbh=swcljg.xcbh				and"+
				" stsd.xcbh=wtxc.xcbh   			and"+
				" stsd.xcbh=xcjgd.xcbh  			and " +
				" stsd.swjg_dm_stf=stxc.swjg_dm_stf  "+where_str+" and ROWNUM <= "+rows+
				" ) table_xc "+
				"WHERE table_xc.rowno >="+page;

			System.out.println("朝秦暮楚-Remote_xczh_sql=" + Remote_xczh_sql);

			total_a	=	DB.get_Remote_count(con_a, sql_count, "total");


		try
		{
			caseAssitCheck_list	= DB.executeQuery(con_b, Remote_xczh_sql,CaseAssitCheck.class);
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}

		if(null!=caseAssitCheck_list&&caseAssitCheck_list.size()>0)
		{
				//return ll.get(0);
				EsayJsonEn esayJsonEn=new EsayJsonEn();

				esayJsonEn.setPage(1);
				esayJsonEn.setRows(caseAssitCheck_list);
				esayJsonEn.setTotal(total_a);

				String esayJsonEn_JsonStr= SessionJson.getJsonStr(esayJsonEn);
				System.out.println("esayJsionEn_JsonStr"+esayJsonEn_JsonStr);
				SessionJson.JsonWrite(esayJsonEn_JsonStr);
		}
		else{System.out.println("esayJsionEn_JsonStr=null");
				//return null;
			}

		}








	public IReportIllegalLogic getReportIllegalLogic() {		return reportIllegalLogic;	}
	public void setReportIllegalLogic(IReportIllegalLogic reportIllegalLogic) {		this.reportIllegalLogic = reportIllegalLogic;	}



	public String getOraaddress() {
		return oraaddress;
	}

	public void setOraaddress(String oraaddress) {
		this.oraaddress = oraaddress;
	}

	public String getOrauser() {
		return orauser;
	}

	public void setOrauser(String orauser) {
		this.orauser = orauser;
	}

	public String getOrapwd() {
		return orapwd;
	}

	public void setOrapwd(String orapwd) {
		this.orapwd = orapwd;
	}


}

